USE [ITViet]
GO

-------------------------------------------------------------------------
--						@tblSurvey
-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblSurvey_GetActiveByLanguage]') and type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblSurvey_GetActiveByLanguage]
GO

CREATE PROCEDURE [dbo].[_tblSurvey_GetActiveByLanguage]
(
	@LanguageId INT
)
AS		
	SELECT *
	FROM tblSurvey
	WHERE [IsActive] = 1 AND [LanguageId] = @LanguageId
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblSurvey_GetByLanguage]') and type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblSurvey_GetByLanguage]
GO

CREATE PROCEDURE [dbo].[_tblSurvey_GetByLanguage]
(
	@LanguageId INT
)
AS
	SELECT *, [SurveyResult] ='', [ShowOnHome]='', [DisplayType]=''
	FROM tblSurvey
	WHERE [LanguageId] = @LanguageId

	SELECT S.[Question], S.[LanguageId], 
				D.*
	FROM tblSurvey S
				JOIN tblSurveyDetail D ON S.[Id] = D.[SurveyId]
	WHERE S.[LanguageId] = @LanguageId
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblSurvey_SetSurveyActive]') and type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblSurvey_SetSurveyActive]
GO

CREATE PROCEDURE [dbo].[_tblSurvey_SetSurveyActive]
(
	@SurveyId INT
)
AS
BEGIN TRAN
	DECLARE @LanguageId INT
	SET @LanguageId = (SELECT [LanguageId] FROM tblSurvey WHERE [Id] = @SurveyId)
	UPDATE tblSurvey
	SET [IsActive] = 0
	WHERE [Id] <>  @SurveyId AND [LanguageId] = @LanguageId

	IF(@@ERROR<>0)
		GOTO HANDLEERR

	UPDATE tblSurvey
	SET [IsActive] = 1
	WHERE [Id] = @SurveyId 

	IF(@@ERROR<>0)
	GOTO HANDLEERR

COMMIT TRAN
RETURN
HANDLEERR:
	ROLLBACK TRAN	
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblSurveyDetail_IncreaseValue]') and type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[_tblSurveyDetail_IncreaseValue]
GO

CREATE PROCEDURE [dbo].[_tblSurveyDetail_IncreaseValue]
(
	@Id INT
)
AS
	UPDATE tblSurveyDetail
	SET [Value] = CAST((CAST(ISNULL([Value],'0') AS INT) + 1) AS NVARCHAR(512)),
			[LastModified] = getDate()
	WHERE [Id] = @Id
				AND [Type] = 'C'
GO
